#!/use/bin/python
# coding=utf-8
# 数据分析-主动服务-7日留存率 (需要执行一次)
#KEY:
# LOG_DIVICE_ACTIVE_V1_* 为服务当前总订阅量  LOG_DIVICE_NEW_ACTIVE_V1_* 为服务当天订阅量
import redis
import datetime
import pymysql

from dbutils.pooled_db import PooledDB

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# redis
def getReidsConnection():
    redis_info = {
        "host": "172.20.151.90",
        "password": "Coocaa2022",
        "port": 6379,
        "db": 8
    }

    r = redis.Redis(**redis_info, decode_responses=True)
    return r

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql):
    conn, cur = getDataConnection()
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num
    return countNum

#计算百分比 保留两位小数  如:34.88%
# X为分子 Y为分母
def getRateByXY(X,Y):
    rate = 0
    if(X is None or Y is None):
        return rate
    if Y != 0:
        if X > Y:
            rate = 100
        else:
            rate = round(X / Y, 4) * 100
    return rate

# 记录 主动服务整体 7日留存率 - 累计版
def saveAllRetentionRate(date,before7Day):
    try:
        conn, cur = getDataConnection()
        r = getReidsConnection()
        # 判断 analysis_retention_rate 表是否已经存在当天 数据
        selectCountSql = """
                            select count(1) num from analysis_retention_rate t where 1=1 and t.ref_date =  '%s' and t.type =1 and execute_flag = 1
                        """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        #将当天及 7天前的设备差进行比较
        key = "LOG_DIVICE_ACTIVE_V1_ALL_" + date.replace('-','')
        before7Daykey = "LOG_DIVICE_ACTIVE_V1_ALL_" + before7Day.replace('-','')
        #N-7日服务处于打开的用户数
        open_num = r.scard(before7Daykey)
        # 当天处于打开的用户数
        open_num_7 = r.scard(key);
        #N-7日且N日服务处于打开的用户数
        open_7day_num = len(r.sinter(key, before7Daykey))
        retentionRate = 0
        if open_num!=0:
            retentionRate = round(open_7day_num/open_num,4)*100

        if countNum == 0:
            logDetail = "累计版-%s日新增-查询:key:%s数量为%s,key:%s数量为%s,主动服务7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                  date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_retention_rate(retention_rate,open_num,open_7day_num,ref_date,type,execute_flag) 
                       value(%s,%s,%s,'%s',%s,1)""" % (retentionRate, open_num, open_7day_num, date, 1)
            cur.execute(insertSql)
        else:
            logDetail = "累计版-%s日更新-查询:key:%s数量为%s,key:%s数量为%s,主动服务7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                  date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                       update analysis_retention_rate t set retention_rate = %s ,open_num = %s,open_7day_num = %s  ,update_time = now()  where
                           ref_date = '%s' and t.type = 1 and execute_flag = 1
                   """ % (retentionRate, open_num, open_7day_num, date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 早间服务 7日留存率 - 累计版
def saveZjkjRetentionRate(date,before7Day):
    try:
        conn, cur = getDataConnection()
        r = getReidsConnection()
        # 判断 analysis_zjkj_retention_rate 表是否已经存在当天 数据
        selectCountSql = """
                            select count(1) num from analysis_zjkj_retention_rate t where 1=1 and t.ref_date =  '%s' and t.type =1 and execute_flag = 1
                        """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        #将当天及 7天前的设备差进行比较
        key = "LOG_DIVICE_ACTIVE_V1_ZJKJ_" + date.replace('-','')
        before7Daykey = "LOG_DIVICE_ACTIVE_V1_ZJKJ_" + before7Day.replace('-','')
        #N-7日服务处于打开的用户数
        open_num = r.scard(before7Daykey)
        # 当天处于打开的用户数
        open_num_7 = r.scard(key);
        #N-7日且N日服务处于打开的用户数
        open_7day_num = len(r.sinter(key, before7Daykey))
        retentionRate = 0
        if open_num!=0:
            retentionRate = round(open_7day_num/open_num,4)*100

        if countNum == 0:
            logDetail = "累计版-%s日新增-查询:key:%s数量为%s,key:%s数量为%s,早间开机7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                  date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_zjkj_retention_rate(retention_rate,open_num,open_7day_num,ref_date,type,execute_flag) 
                       value(%s,%s,%s,'%s',%s,1)""" % (retentionRate, open_num, open_7day_num, date, 1)
            cur.execute(insertSql)
        else:
            logDetail = "累计版-%s日更新-查询:key:%s数量为%s,key:%s数量为%s,早间开机7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                  date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                       update analysis_zjkj_retention_rate t set retention_rate = %s ,open_num = %s,open_7day_num = %s  ,update_time = now()  where
                           ref_date = '%s' and t.type = 1 and execute_flag = 1
                   """ % (retentionRate, open_num, open_7day_num, date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 健康作息 7日留存率 - 累计版
def saveJkzxRetentionRate(date,before7Day):
    try:
        conn, cur = getDataConnection()
        r = getReidsConnection()
        # 判断 analysis_zjkj_retention_rate 表是否已经存在当天 数据
        selectCountSql = """
                            select count(1) num from analysis_jkzx_retention_rate t where 1=1 and t.ref_date =  '%s' and t.type =1 and execute_flag = 1
                        """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        #将当天及 7天前的设备差进行比较
        key = "LOG_DIVICE_ACTIVE_V1_JKZX_" + date.replace('-','')
        before7Daykey = "LOG_DIVICE_ACTIVE_V1_JKZX_" + before7Day.replace('-','')
        #N-7日服务处于打开的用户数
        open_num = r.scard(before7Daykey)
        # 当天处于打开的用户数
        open_num_7 = r.scard(key);
        #N-7日且N日服务处于打开的用户数
        open_7day_num = len(r.sinter(key, before7Daykey))
        retentionRate = 0
        if open_num!=0:
            retentionRate = round(open_7day_num/open_num,4)*100

        if countNum == 0:
            logDetail = "累计版-%s日新增-查询:key:%s数量为%s,key:%s数量为%s,健康作息7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                  date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_jkzx_retention_rate(retention_rate,open_num,open_7day_num,ref_date,type,execute_flag) 
                       value(%s,%s,%s,'%s',%s,1)""" % (retentionRate, open_num, open_7day_num, date, 1)
            cur.execute(insertSql)
        else:
            logDetail = "累计版-%s日更新-查询:key:%s数量为%s,key:%s数量为%s,健康作息7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                  date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                       update analysis_jkzx_retention_rate t set retention_rate = %s ,open_num = %s,open_7day_num = %s  ,update_time = now()  where
                           ref_date = '%s' and t.type = 1 and execute_flag = 1
                   """ % (retentionRate, open_num, open_7day_num, date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 夜间音量 7日留存率 - 累计版
def saveYjylRetentionRate(date,before7Day):
    try:
        conn, cur = getDataConnection()
        r = getReidsConnection()
        # 判断 analysis_yjyl_retention_rate 表是否已经存在当天 数据
        selectCountSql = """
                            select count(1) num from analysis_yjyl_retention_rate t where 1=1 and t.ref_date =  '%s' and t.type =1 and execute_flag = 1
                        """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        #将当天及 7天前的设备差进行比较
        key = "LOG_DIVICE_ACTIVE_V1_YJYL_" + date.replace('-','')
        before7Daykey = "LOG_DIVICE_ACTIVE_V1_YJYL_" + before7Day.replace('-','')
        #N-7日服务处于打开的用户数
        open_num = r.scard(before7Daykey)
        # 当天处于打开的用户数
        open_num_7 = r.scard(key);
        #N-7日且N日服务处于打开的用户数
        open_7day_num = len(r.sinter(key, before7Daykey))
        retentionRate = 0
        if open_num!=0:
            retentionRate = round(open_7day_num/open_num,4)*100

        if countNum == 0:
            logDetail = "累计版-%s日新增-查询:key:%s数量为%s,key:%s数量为%s,夜间音量7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                  date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_yjyl_retention_rate(retention_rate,open_num,open_7day_num,ref_date,type,execute_flag) 
                       value(%s,%s,%s,'%s',%s,1)""" % (retentionRate, open_num, open_7day_num, date, 1)
            cur.execute(insertSql)
        else:
            logDetail = "累计版-%s日更新-查询:key:%s数量为%s,key:%s数量为%s,夜间音量7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                  date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                       update analysis_yjyl_retention_rate t set retention_rate = %s ,open_num = %s,open_7day_num = %s  ,update_time = now()  where
                           ref_date = '%s'  and t.type = 1 and execute_flag = 1
                   """ % (retentionRate, open_num, open_7day_num, date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 追剧提醒 7日留存率 - 累计版
def saveZjtxRetentionRate(date,before7Day):
    try:
        conn, cur = getDataConnection()
        r = getReidsConnection()
        # 判断 analysis_zjtx_retention_rate 表是否已经存在当天 数据
        selectCountSql = """
                            select count(1) num from analysis_zjtx_retention_rate t where 1=1 and t.ref_date =  '%s' and t.type =1 and execute_flag = 1
                        """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        #将当天及 7天前的设备差进行比较
        key = "LOG_DIVICE_ACTIVE_V1_ZJTX_" + date.replace('-','')
        before7Daykey = "LOG_DIVICE_ACTIVE_V1_ZJTX_" + before7Day.replace('-','')
        #N-7日服务处于打开的用户数
        open_num = r.scard(before7Daykey)
        # 当天处于打开的用户数
        open_num_7 = r.scard(key);
        #N-7日且N日服务处于打开的用户数
        open_7day_num = len(r.sinter(key, before7Daykey))
        retentionRate = 0
        if open_num!=0:
            retentionRate = round(open_7day_num/open_num,4)*100

        if countNum == 0:
            logDetail = "累计版-%s日新增-查询:key:%s数量为%s,key:%s数量为%s,追剧提醒7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                  date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_zjtx_retention_rate(retention_rate,open_num,open_7day_num,ref_date,type,execute_flag) 
                       value(%s,%s,%s,'%s',%s,1)""" % (retentionRate, open_num, open_7day_num, date, 1)
            cur.execute(insertSql)
        else:
            logDetail = "累计版-%s日更新-查询:key:%s数量为%s,key:%s数量为%s,追剧提醒7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                  date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                       update analysis_zjtx_retention_rate t set retention_rate = %s ,open_num = %s,open_7day_num = %s  ,update_time = now()  where
                           ref_date = '%s'  and t.type = 1 and execute_flag = 1
                   """ % (retentionRate, open_num, open_7day_num, date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 主动服务整体 7日留存率 - 新增版
def saveAllNewRetentionRate(date,before7Day):
    try:
        conn, cur = getDataConnection()
        r = getReidsConnection()
        # 判断 analysis_retention_rate 表是否已经存在当天 数据
        selectCountSql = """
                            select count(1) num from analysis_retention_rate t where 1=1 and t.ref_date =  '%s' and t.type = 2 and execute_flag = 1
                        """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        #将当天及 7天前的设备差进行比较
        key = "LOG_DIVICE_ACTIVE_V1_ALL_" + date.replace('-','')
        before7Daykey = "LOG_DIVICE_NEW_ACTIVE_V1_ALL_" + before7Day.replace('-','')
        #N-7日服务处于打开的用户数
        open_num = r.scard(before7Daykey)
        # 当天处于打开的用户数
        open_num_7 = r.scard(key);
        #N-7日且N日服务处于打开的用户数
        open_7day_num = len(r.sinter(key, before7Daykey))
        retentionRate = 0
        if open_num!=0:
            retentionRate = round(open_7day_num/open_num,4)*100

        if countNum == 0:
            logDetail = "新增版-%s日新增-查询:key:%s数量为%s,key:%s数量为%s,主动服务7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                  date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_retention_rate(retention_rate,open_num,open_7day_num,ref_date,type,execute_flag) 
                       value(%s,%s,%s,'%s',%s,1)""" % (retentionRate, open_num, open_7day_num, date, 2)
            cur.execute(insertSql)
        else:
            logDetail = "新增版-%s日更新-查询:key:%s数量为%s,key:%s数量为%s,主动服务7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                      date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                       update analysis_retention_rate t set retention_rate = %s ,open_num = %s,open_7day_num = %s  ,update_time = now()  where
                           ref_date = '%s' and t.type = 2 and execute_flag = 1
                   """ % (retentionRate, open_num, open_7day_num, date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 早间服务 7日留存率 - 新增版
def saveZjkjNewRetentionRate(date,before7Day):
    try:
        conn, cur = getDataConnection()
        r = getReidsConnection()
        # 判断 analysis_zjkj_retention_rate 表是否已经存在当天 数据
        selectCountSql = """
                            select count(1) num from analysis_zjkj_retention_rate t where 1=1 and t.ref_date =  '%s' and t.type = 2 and execute_flag = 1
                        """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        #将当天及 7天前的设备差进行比较
        key = "LOG_DIVICE_ACTIVE_V1_ZJKJ_" + date.replace('-','')
        before7Daykey = "LOG_DIVICE_NEW_ACTIVE_V1_ZJKJ_" + before7Day.replace('-','')
        #N-7日服务处于打开的用户数
        open_num = r.scard(before7Daykey)
        # 当天处于打开的用户数
        open_num_7 = r.scard(key);
        #N-7日且N日服务处于打开的用户数
        open_7day_num = len(r.sinter(key, before7Daykey))
        retentionRate = 0
        if open_num!=0:
            retentionRate = round(open_7day_num/open_num,4)*100

        if countNum == 0:
            logDetail = "新增版-%s日新增-查询:key:%s数量为%s,key:%s数量为%s,早间开机7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                      date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_zjkj_retention_rate(retention_rate,open_num,open_7day_num,ref_date,type,execute_flag) 
                       value(%s,%s,%s,'%s',%s,1)""" % (retentionRate, open_num, open_7day_num, date, 2)
            cur.execute(insertSql)
        else:
            logDetail = "新增版-%s日更新-查询:key:%s数量为%s,key:%s数量为%s,早间开机7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                      date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                       update analysis_zjkj_retention_rate t set retention_rate = %s ,open_num = %s,open_7day_num = %s  ,update_time = now()  where
                           ref_date = '%s' and t.type = 2 and execute_flag = 1
                   """ % (retentionRate, open_num, open_7day_num, date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 健康作息 7日留存率 - 新增版
def saveJkzxNewRetentionRate(date,before7Day):
    try:
        conn, cur = getDataConnection()
        r = getReidsConnection()
        # 判断 analysis_zjkj_retention_rate 表是否已经存在当天 数据
        selectCountSql = """
                            select count(1) num from analysis_jkzx_retention_rate t where 1=1 and t.ref_date =  '%s' and t.type = 2 and execute_flag = 1
                        """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        #将当天及 7天前的设备差进行比较
        key = "LOG_DIVICE_ACTIVE_V1_JKZX_" + date.replace('-','')
        before7Daykey = "LOG_DIVICE_NEW_ACTIVE_V1_JKZX_" + before7Day.replace('-','')
        #N-7日服务处于打开的用户数
        open_num = r.scard(before7Daykey)
        # 当天处于打开的用户数
        open_num_7 = r.scard(key);
        #N-7日且N日服务处于打开的用户数
        open_7day_num = len(r.sinter(key, before7Daykey))
        retentionRate = 0
        if open_num!=0:
            retentionRate = round(open_7day_num/open_num,4)*100

        if countNum == 0:
            logDetail = "新增版-%s日新增-查询:key:%s数量为%s,key:%s数量为%s,健康作息7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                      date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_jkzx_retention_rate(retention_rate,open_num,open_7day_num,ref_date,type,execute_flag) 
                       value(%s,%s,%s,'%s',%s,1)""" % (retentionRate, open_num, open_7day_num, date, 2)
            cur.execute(insertSql)
        else:
            logDetail = "新增版-%s日修改-查询:key:%s数量为%s,key:%s数量为%s,健康作息7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                      date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                       update analysis_jkzx_retention_rate t set retention_rate = %s ,open_num = %s,open_7day_num = %s  ,update_time = now()  where
                           ref_date = '%s' and t.type = 2 and execute_flag = 1
                   """ % (retentionRate, open_num, open_7day_num, date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 夜间音量 7日留存率 - 新增版
def saveYjylNewRetentionRate(date,before7Day):
    try:
        conn, cur = getDataConnection()
        r = getReidsConnection()
        # 判断 analysis_yjyl_retention_rate 表是否已经存在当天 数据
        selectCountSql = """
                            select count(1) num from analysis_yjyl_retention_rate t where 1=1 and t.ref_date =  '%s' and t.type = 2 and execute_flag = 1
                        """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        #将当天及 7天前的设备差进行比较
        key = "LOG_DIVICE_ACTIVE_V1_YJYL_" + date.replace('-','')
        before7Daykey = "LOG_DIVICE_NEW_ACTIVE_V1_YJYL_" + before7Day.replace('-','')
        #N-7日服务处于打开的用户数
        open_num = r.scard(before7Daykey)
        # 当天处于打开的用户数
        open_num_7 = r.scard(key);
        #N-7日且N日服务处于打开的用户数
        open_7day_num = len(r.sinter(key, before7Daykey))
        retentionRate = 0
        if open_num!=0:
            retentionRate = round(open_7day_num/open_num,4)*100

        if countNum == 0:
            logDetail = "新增版-%s日新增-查询:key:%s数量为%s,key:%s数量为%s,夜间音量7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                      date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_yjyl_retention_rate(retention_rate,open_num,open_7day_num,ref_date,type,execute_flag) 
                       value(%s,%s,%s,'%s',%s,1)""" % (retentionRate, open_num, open_7day_num, date, 2)
            cur.execute(insertSql)
        else:
            logDetail = "新增版-%s日更新-查询:key:%s数量为%s,key:%s数量为%s,夜间音量7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                      date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                       update analysis_yjyl_retention_rate t set retention_rate = %s ,open_num = %s,open_7day_num = %s  ,update_time = now()  where
                           ref_date = '%s' and t.type = 2 and execute_flag = 1
                   """ % (retentionRate, open_num, open_7day_num, date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 追剧提醒 7日留存率 - 新增版
def saveZjtxNewRetentionRate(date,before7Day):
    try:
        conn, cur = getDataConnection()
        r = getReidsConnection()
        # 判断 analysis_zjtx_retention_rate 表是否已经存在当天 数据
        selectCountSql = """
                            select count(1) num from analysis_zjtx_retention_rate t where 1=1 and t.ref_date =  '%s' and t.type = 2 and execute_flag = 1
                        """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        #将当天及 7天前的设备差进行比较
        key = "LOG_DIVICE_ACTIVE_V1_ZJTX_" + date.replace('-','')
        before7Daykey = "LOG_DIVICE_NEW_ACTIVE_V1_ZJTX_" + before7Day.replace('-','')
        #N-7日服务处于打开的用户数
        open_num = r.scard(before7Daykey)
        # 当天处于打开的用户数
        open_num_7 = r.scard(key);
        #N-7日且N日服务处于打开的用户数
        open_7day_num = len(r.sinter(key, before7Daykey))
        retentionRate = 0
        if open_num!=0:
            retentionRate = round(open_7day_num/open_num,4)*100

        if countNum == 0:
            logDetail = "新增版-%s日新增-查询:key:%s数量为%s,key:%s数量为%s,追剧提醒7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                      date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_zjtx_retention_rate(retention_rate,open_num,open_7day_num,ref_date,type,execute_flag) 
                       value(%s,%s,%s,'%s',%s,1)""" % (retentionRate, open_num, open_7day_num, date, 2)
            cur.execute(insertSql)
        else:
            logDetail = "新增版-%s日更新-查询:key:%s数量为%s,key:%s数量为%s,追剧提醒7天前服务处于打开的用户数为%s,7天前且当天服务处于打开的用户数为%s,当天7日留存率为%s" % (
                      date, before7Daykey, open_num, key, open_num_7, open_num, open_7day_num, retentionRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                       update analysis_zjtx_retention_rate t set retention_rate = %s ,open_num = %s,open_7day_num = %s  ,update_time = now()  where
                           ref_date = '%s' and t.type = 2 and execute_flag = 1
                   """ % (retentionRate, open_num, open_7day_num, date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 7日留存率 - type3
def saveNewRetentionRateType3(date,serId):
    try:
        conn, cur = getDataConnection()
        r = getReidsConnection()
        # 判断 analysis_zjtx_retention_rate 表是否已经存在当天 数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_{serId.lower()}_retention_rate t where 1=1 and t.ref_date =  '{date}' and t.type = 3 and execute_flag = 1 ")
        #将满足7天留存设备量 跟 总订阅量 相除 计算比例
        onceKey = f"LOG_DIVICE_ACTIVE_V1_ONCE_{serId}"
        retentionKey = f"LOG_DIVICE_ACTIVE_V1_RETENTION_{serId}"
        onceKeyNum = r.scard(onceKey);
        retentionKeyNum = r.scard(retentionKey);
        retentionRate = getRateByXY(retentionKeyNum,onceKeyNum)

        serName = serIdToName(serId)
        if countNum == 0:
            logDetail = f"最新版-{date}日{serName}新增-总订阅量为{onceKeyNum},满足7天留存设备量为{retentionKeyNum},比例为{retentionRate}"
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = f"""insert into analysis_{serId.lower()}_retention_rate(retention_rate,open_num,open_7day_num,ref_date,type,execute_flag) 
                       value({retentionRate},{onceKeyNum},{retentionKeyNum},'{date}',3,1)"""
            cur.execute(insertSql)
        else:
            logDetail = f"最新版-{date}日{serName}更新-总订阅量为{onceKeyNum},满足7天留存设备量为{retentionKeyNum},比例为{retentionRate}"
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = f"""
                       update analysis_{serId.lower()}_retention_rate t set retention_rate = {retentionRate} ,open_num = {onceKeyNum},open_7day_num = {retentionKeyNum}  ,update_time = now()  where
                           ref_date = '{date}' and t.type = 3 and execute_flag = 1 
                   """
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

#根据serId 获取服务中文名
def serIdToName(serId):
    if serId =='ZJKJ':
        serName = "早间开机"
    elif serId =='JKZX':
        serName = "健康作息"
    elif serId =='YJYL':
        serName = "夜间音量"
    elif serId =='ZJTX':
        serName = "追剧提醒"
    else:
        serName = "其他"
    return serName

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 35
        jobName = '核心指标-生活助手-7日留存率(需要执行一次)'
        tableName = 'analysis_retention_rate'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    #today = todayYMD()
    date = getdate(1)
    before7Day = getdate(8)
    #today = "2021-12-13"
    print ("%s 日期,执行服务留存率(需要执行一次)报表" % date)
    #saveAllRetentionRate(date,before7Day)
    #saveZjkjRetentionRate(date,before7Day)
    #saveJkzxRetentionRate(date,before7Day)
    #saveYjylRetentionRate(date,before7Day)
    #saveZjtxRetentionRate(date,before7Day)
    #saveAllNewRetentionRate(date,before7Day)
    #saveZjkjNewRetentionRate(date,before7Day)
    #saveJkzxNewRetentionRate(date,before7Day)
    #saveYjylNewRetentionRate(date,before7Day)
    #saveZjtxNewRetentionRate(date,before7Day)
    saveNewRetentionRateType3(date,'ZJKJ')
    saveNewRetentionRateType3(date,'YJYL')
    saveNewRetentionRateType3(date,'JKZX')
    saveNewRetentionRateType3(date,'ZJTX')

